Covering Index로 테이블 접근 줄이기

Covering Index로 테이블 접근 줄이기

한눈에 보기

Covering Index는 별도의 특별한 인덱스 종류가 아니라, 한 쿼리가 필요한 조건·정렬·결과 열을 특정 인덱스만으로 얻을 수 있는 상태를 말한다. 보조 인덱스에서 찾은 기본 키로 본 테이블을 다시 읽는 횟수를 줄여 목록·집계 쿼리를 빠르게 만들 수 있지만, 결과 열을 무작정 추가하면 인덱스가 커지고 모든 쓰기 비용이 증가한다.

목차

문제가 되는 상황

주문 목록 화면에는 주문 ID, 생성 시각, 상태만 필요하다고 하자.

SELECT id, created_at, status
FROM orders
WHERE user_id = 8421
ORDER BY created_at DESC
LIMIT 20;

다음 인덱스가 있으면 사용자별 주문을 최신순으로 찾을 수 있다.

CREATE INDEX ix_orders_user_created
ON orders(user_id, created_at DESC);

조건과 정렬이 인덱스에 맞으니 충분해 보인다. 하지만 status는 이 보조 인덱스에 없다. DB는 인덱스에서 후보 엔트리를 찾은 뒤 각 주문의 전체 행이 있는 위치로 이동해 status를 읽어야 한다.

결과 20건이면 추가 접근도 20번에 그칠 수 있다. 그러나 수천 건을 내보내는 배치나, 조건으로 많이 읽은 뒤 일부만 남기는 쿼리라면 lookup이 누적된다. 버퍼 풀에 데이터 페이지가 없으면 랜덤 I/O가 되고, 메모리에 있어도 다른 B+Tree와 페이지를 반복 탐색하는 CPU 비용이 생긴다.

보조 인덱스 탐색 뒤에 한 번 더 읽는 이유

InnoDB를 기준으로 개념을 단순화하면 클러스터드 인덱스의 리프에는 실제 행이 있고, 보조 인덱스의 리프에는 보조 키와 기본 키가 있다.

Secondary index leaf: ix_orders_user_created
[user_id=8421 | created_at=2026-09-01 | primary_key=501]

Primary/clustered index leaf
[id=501 | user_id=8421 | status=PAID | amount=32000 | ...]

쿼리 실행 흐름은 다음처럼 된다.

flowchart LR
    A[보조 인덱스에서 user_id 탐색] --> B[PK 501, 498, 490 ...]
    B --> C[기본 키 인덱스 재탐색]
    C --> D[status 포함 전체 행 읽기]

보조 인덱스가 20개의 기본 키를 반환하면 기본 키 B+Tree를 최대 20번 다시 찾아갈 수 있다. 이 과정을 table lookup, row lookup, back to table 등으로 설명한다. 정확한 실행 방식과 최적화는 엔진마다 다르지만, 핵심은 결과에 필요한 열이 보조 인덱스에 없으면 다른 저장 구조를 추가로 읽어야 한다는 것이다.

Covering은 인덱스가 아니라 쿼리의 속성이다

다음 인덱스를 만든다.

CREATE INDEX ix_orders_user_created_status
ON orders(user_id, created_at DESC, status);

앞의 목록 쿼리는 조건, 정렬, SELECT 열을 모두 이 인덱스에서 얻을 수 있다. 이때 인덱스가 쿼리를 cover한다고 말한다.

WHERE:    user_id       → index에 있음
ORDER BY: created_at    → index에 있음
SELECT:   id            → InnoDB 보조 인덱스의 PK
          created_at    → index에 있음
          status        → index에 있음

그러나 같은 인덱스도 다음 쿼리는 cover하지 못한다.

SELECT id, created_at, status, shipping_address
FROM orders
WHERE user_id = 8421
ORDER BY created_at DESC
LIMIT 20;

shipping_address가 없기 때문이다. 즉 “이 인덱스는 covering index다”보다 “이 인덱스가 이 쿼리를 cover한다”라고 표현하는 것이 정확하다.

필터에 필요한 열도 포함돼야 한다

SELECT 목록만 인덱스에 있다고 끝이 아니다. WHERE에서 엔진이 평가해야 하는 열과 정렬·그룹화에 필요한 열까지 인덱스에서 해결할 수 있어야 본 테이블 접근을 피할 수 있다.

목록 조회를 covering으로 바꾸는 과정

가상의 주문 목록 요구사항을 단계별로 살펴보자.

SELECT id, created_at, status
FROM orders
WHERE user_id = :user_id
  AND status IN ('PAID', 'SHIPPED')
ORDER BY created_at DESC
LIMIT 20;

후보 1은 다음과 같다.

INDEX (user_id, status, created_at)

user와 status별 범위는 잘 좁힐 수 있다. 그러나 두 status 구간을 합친 created_at 전체 정렬은 별도 병합 또는 정렬이 필요할 수 있다.

후보 2는 다음과 같다.

INDEX (user_id, created_at, status)

user의 최신 주문부터 순서대로 읽으며 status를 인덱스에서 필터링할 수 있다. PAID와 SHIPPED 비율이 높고 LIMIT 20을 빨리 채운다면 유리할 수 있다. 반대로 대부분이 다른 status라면 많은 인덱스 엔트리를 읽고 버리게 된다.

이처럼 covering 여부만으로 열 순서를 결정해서는 안 된다. 앞 열은 탐색과 정렬 성능을 결정하고, 뒤 열은 결과 반환과 필터를 위해 추가되는 경우가 많다.

탐색 접두: user_id, created_at
추가 payload: status

인덱스를 만들기 전에 두 후보의 실제 읽은 행 수, 정렬 여부, 테이블 lookup 수를 비교해야 한다. 왼쪽 접두와 정렬 관계는 복합 인덱스의 왼쪽 접두 규칙에서 더 자세히 다뤘다.

기본 키는 이미 포함되어 있을 수 있다

InnoDB 보조 인덱스는 대상 행을 찾기 위해 기본 키를 함께 보관한다. 따라서 인덱스 정의에 id가 보이지 않아도 다음 쿼리가 cover될 수 있다.

CREATE INDEX ix_orders_user_created_status
ON orders(user_id, created_at, status);

SELECT id, created_at, status
FROM orders
WHERE user_id = 8421;

이를 모르고 다음처럼 기본 키를 명시적으로 뒤에 추가하면 중복될 수 있다.

CREATE INDEX ix_orders_user_created_status_id
ON orders(user_id, created_at, status, id);

다만 이 동작을 모든 DB 엔진에 일반화하면 안 된다. 테이블 저장 구조와 보조 인덱스가 행을 가리키는 방식이 다를 수 있으므로 대상 DB의 공식 문서와 실행 계획을 기준으로 판단한다.

또한 기본 키가 길면 모든 보조 인덱스 리프가 커진다. UUID 문자열처럼 넓은 기본 키를 사용하면서 보조 인덱스가 많다면 캐시 효율과 저장 공간에도 영향을 준다.

Using index와 Index Condition Pushdown은 다르다

MySQL 실행 계획의 Extra에 나타나는 용어는 비슷해 혼동하기 쉽다.

신호 개념적 의미
Using index 필요한 값을 인덱스에서 얻는 covering access 가능성
Using index condition 스토리지 엔진이 인덱스 엔트리에서 조건을 먼저 평가하는 Index Condition Pushdown
Using where 서버 계층 등에서 추가 조건 필터가 필요함

예를 들어 인덱스에 필터 열은 있지만 SELECT 결과 열이 없을 수 있다.

CREATE INDEX ix_orders_user_created_status
ON orders(user_id, created_at, status);

SELECT shipping_address
FROM orders
WHERE user_id = 8421
  AND created_at >= '2026-08-01'
  AND status = 'PAID';

status를 인덱스에서 먼저 평가해 PAID가 아닌 행은 본 테이블 lookup 전에 버릴 수 있다. 이것은 lookup 수를 줄이는 중요한 최적화지만, shipping_address를 읽으려면 최종 후보의 테이블 행에는 접근해야 하므로 완전한 covering은 아니다.

실행 계획 문구만 암기하지 않는다

DB 버전과 출력 형식에 따라 표현이 달라진다. 실제 실행 계획에서 인덱스 조건, 필터 후 행 수, table access 단계가 어떻게 연결되는지를 본다.

SELECT *가 covering을 어렵게 만든다

목록 API에서 편의상 SELECT *를 사용하면 테이블에 열이 하나 추가될 때마다 반환 크기가 늘고, 기존 covering 가능성도 사라진다.

SELECT *
FROM orders
WHERE user_id = 8421
ORDER BY created_at DESC
LIMIT 20;

테이블에는 목록에서 필요하지 않은 긴 메모, 배송지 JSON, 내부 처리 상태가 포함될 수 있다. 모든 열을 인덱스에 넣는 것은 해결책이 아니다. 화면 또는 API에 필요한 projection을 명시하는 편이 좋다.

SELECT id, status, created_at, total_amount
FROM orders
WHERE user_id = :user_id
ORDER BY created_at DESC
LIMIT 20;

type OrderListItem = {
  id: number;
  status: OrderStatus;
  createdAt: Date;
  totalAmount: number;
};

이렇게 읽기 모델을 작게 만들면 네트워크 전송과 객체 생성 비용도 줄고, covering 후보를 현실적으로 검토할 수 있다.

큰 열을 인덱스에 넣으면 생기는 비용

Covering을 만들겠다고 TEXT, 긴 VARCHAR, JSON 같은 열을 인덱스에 추가하면 인덱스 페이지당 엔트리 수가 줄어든다.

작은 엔트리: 한 leaf page에 많은 키 저장
큰 엔트리:   한 leaf page에 적은 키 저장

→ 더 많은 leaf page
→ 버퍼 풀 점유 증가
→ 트리 높이 또는 범위 I/O 증가 가능
→ INSERT/UPDATE 비용 증가

특히 자주 바뀌는 status나 결과 열을 인덱스에 포함하면 해당 열 변경 때 보조 인덱스도 갱신해야 한다. covering으로 절약한 읽기보다 쓰기 비용이 더 클 수 있다.

추가 열 기대 효과 비용·주의점
작은 상태 코드 목록 lookup 감소 상태 변경마다 인덱스 갱신
금액 숫자 집계·목록 cover 가능 인덱스 폭 증가
긴 제목 일부 목록 cover 페이지당 엔트리 감소, 길이 제한
JSON·본문 거의 모든 열 반환 가능 일반적으로 지나치게 큰 비용

필요한 큰 내용은 목록에서 제외하고 상세 조회에서 기본 키로 한 행만 읽는 것이 더 자연스럽다.

LIMIT과 페이지네이션에서 효과가 커지는 경우

인덱스 순서가 WHERE·ORDER BY와 맞고 필요한 열까지 cover하면 DB는 앞에서 필요한 개수만 읽고 멈출 수 있다.

SELECT id, created_at, status
FROM orders
WHERE user_id = :user_id
  AND (created_at, id) < (:cursor_time, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

CREATE INDEX ix_orders_user_created_id_status
ON orders(user_id, created_at DESC, id DESC, status);

이론적으로는 커서 다음 위치를 찾아 20개의 인덱스 엔트리만 읽고 결과를 만들 수 있다. OFFSET이 커질수록 앞의 결과를 건너뛰어야 하는 방식보다 효율적이다.

그러나 status 필터처럼 인덱스 순서상 뒤에서 많은 행을 버리면 20건을 얻기 위해 훨씬 많은 엔트리를 읽는다. LIMIT 20이 있다고 항상 20행만 읽는 것은 아니다. 실행 계획의 actual rows와 loops를 확인해야 한다.

COUNT와 존재 여부 조회

전체 행 데이터가 필요 없는 집계와 존재 확인은 작은 인덱스로 처리할 때 이점이 크다.

SELECT COUNT(*)
FROM orders
WHERE user_id = 8421
  AND status = 'PAID';

SELECT 1
FROM orders
WHERE user_id = 8421
  AND status = 'PAID'
LIMIT 1;

(user_id, status) 인덱스가 있으면 조건 구간의 인덱스 엔트리만 읽거나 첫 엔트리에서 멈출 수 있다. 행이 넓은 테이블일수록 작은 보조 인덱스 페이지를 읽는 이점이 커질 수 있다.

애플리케이션에서도 존재 여부만 필요한데 전체 객체를 조회하지 않는다.

// 불필요한 전체 행 로드
const orders = await repository.findPaidOrders(userId);
return orders.length > 0;

// 존재 여부 전용 쿼리
return repository.existsPaidOrder(userId);

다만 중복 방지 같은 비즈니스 규칙은 존재 조회만으로 동시성 안전하지 않다. 최종 보장은 Unique Constraint를 비즈니스 규칙으로 활용하기처럼 제약 조건에 둬야 한다.

MVCC와 잠금 읽기에서의 주의점

인덱스에 필요한 열이 모두 있다고 해서 모든 상황에서 물리적으로 본 테이블 접근이 0이라고 단정할 수는 없다. MVCC 가시성 판단에 필요한 정보, 최신 버전 확인, 삭제 마킹된 레코드, 잠금 읽기 여부에 따라 엔진이 추가 정보를 확인할 수 있다.

SELECT id, status
FROM orders
WHERE user_id = 8421
FOR UPDATE;

FOR UPDATE는 단순한 스냅샷 목록 조회가 아니라 현재 레코드를 잠그려는 요청이다. covering 목적의 읽기 전용 쿼리와 같은 비용 모델로 가정하면 안 된다. InnoDB의 가시성과 잠금 읽기는 MySQL InnoDB의 MVCC가 읽기를 처리하는 방법과 SELECT FOR UPDATE를 사용할 때 주의할 점에서 이어진다.

실행 계획과 측정으로 판단하기

인덱스를 추가하기 전후로 실제 실행 계획을 비교한다.

EXPLAIN ANALYZE
SELECT id, created_at, status
FROM orders
WHERE user_id = 8421
ORDER BY created_at DESC
LIMIT 20;

확인할 질문은 다음과 같다.

테스트 데이터는 행 수뿐 아니라 user별 주문 수와 status 분포가 운영과 비슷해야 한다. 특정 사용자가 주문을 수십만 건 가진 skew가 있다면 평균 20건 데이터로 측정한 결과는 의미가 작다.

읽기 하나만 빠르게 만들고 끝내지 않는다

인덱스 추가 전후의 대표 읽기 쿼리와 대표 INSERT·UPDATE를 같은 환경에서 비교한다. 자주 호출되는 목록의 큰 개선이 쓰기 비용을 감수할 가치가 있는지 수치로 판단한다.

결론

Covering Index의 핵심은 쿼리에 필요한 정보를 인덱스에서 모두 얻어 보조 인덱스와 본 테이블 사이의 반복 lookup을 줄이는 것이다. 목록, 존재 여부, 단순 집계처럼 반환 열이 작고 호출 빈도가 높은 쿼리에서 특히 효과적일 수 있다.

그러나 covering은 인덱스의 영구적인 이름표가 아니라 쿼리별 관계다. SELECT *를 피하고 필요한 projection을 먼저 줄인 뒤, 탐색과 정렬에 필요한 열을 앞에 두고 작은 결과 열을 추가하는 순서로 검토한다. 넓거나 자주 바뀌는 열을 무리하게 포함하면 인덱스 크기, 캐시, 쓰기 비용이 증가한다. 최종 판단은 Using index 문구 하나가 아니라 실제 lookup 수, 읽은 엔트리, 응답 시간과 쓰기 지연을 함께 측정해 내려야 한다.

관련 노트